package net.aib.moneytracker.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import net.aib.moneytracker.db.Interfaces.IDBAdapter;
import net.aib.moneytracker.db.Interfaces.IDBMoneyTracker;

public class DBMoneyTracker implements IDBAdapter, IDBMoneyTracker {
	
	private DBHelper mDBHelper;
	private SQLiteDatabase mDb;
	
	public DBMoneyTracker(Context context){
		mDBHelper = new DBHelper(context);
	}

	public void close() throws SQLException {
		mDBHelper.close();
	}

	public IDBAdapter open() throws SQLException {
		mDb = mDBHelper.getWritableDatabase();
		return this;
	}

	public void addBuyToCompany(long buyId, long companyId) {
		ContentValues buyCompanyValues = new ContentValues();
		buyCompanyValues.put(DBHelper.BUY_KEY_ID, buyId);
		buyCompanyValues.put(DBHelper.COMPANY_KEY_ID, companyId);
		mDb.insert(DBHelper.BUYCOMPANY_TABLE_NAME, null, buyCompanyValues);
	}

	public void addCompanyToCategory(long companyId, long categoryId) {
		ContentValues companyCategoryValues = new ContentValues();
		companyCategoryValues.put(DBHelper.COMPANY_KEY_ID, companyId);
		companyCategoryValues.put(DBHelper.CATEGORY_KEY_ID, categoryId);
		mDb.insert(DBHelper.COMPANYCATEGORY_TABLE_NAME, null, companyCategoryValues);
	}

	public long createBuy(double amount, long id) {
		ContentValues buyValues = new ContentValues();
		buyValues.put(DBHelper.BUY_KEY_AMOUNT, amount);
		long insert = mDb.insert(DBHelper.BUY_TABLE_NAME, null, buyValues);
		ContentValues buyCategoryValues = new ContentValues();
		buyCategoryValues.put(DBHelper.BUY_KEY_ID, insert);
		buyCategoryValues.put(DBHelper.CATEGORY_KEY_ID, id);
		mDb.insert(DBHelper.BUYCATEGORY_TABLE_NAME, null, buyCategoryValues);
		return insert;
	}

	public long createCategory(String categoryName) {
		ContentValues categoryValues = new ContentValues();
		categoryValues.put(DBHelper.CATEGORY_KEY_NAME, categoryName);
		return mDb.insert(DBHelper.CATEGORY_TABLE_NAME, null, categoryValues);
	}

	public long createCompany(String companyName) {
		ContentValues companyValues = new ContentValues();
		companyValues.put(DBHelper.COMPANY_KEY_NAME,companyName);
		return mDb.insert(DBHelper.COMPANY_TABLE_NAME, null, companyValues);
	}

	public boolean deleteBuy(long id) {
		return mDb.delete(DBHelper.BUY_TABLE_NAME, "_id = " + id, null) > 0;
	}

	public boolean deleteCategory(long id) {
		return mDb.delete(DBHelper.CATEGORY_TABLE_NAME, "_id = " + id, null) > 0;
	}

	public boolean deleteCompany(long id) {
		return mDb.delete(DBHelper.COMPANY_TABLE_NAME, "_id = " + id, null) > 0;
	}

	public Cursor fetchBuy(long id) {
		String sql = 
			"SELECT BU._id AS _id, BU." + DBHelper.BUY_KEY_AMOUNT +
			", BCA." + DBHelper.CATEGORY_KEY_ID +
			", BCP." + DBHelper.COMPANY_KEY_ID +
			" FROM " + DBHelper.BUY_TABLE_NAME + " AS BU" +
			" INNER JOIN " + DBHelper.BUYCATEGORY_TABLE_NAME + " AS BCA ON BU._id=BCA." + DBHelper.BUY_KEY_ID +
			" INNER JOIN " + DBHelper.BUYCOMPANY_TABLE_NAME + " AS BCP ON BU._id=BCP." + DBHelper.BUY_KEY_ID +
			" WHERE BU._id = " + id;
		return mDb.rawQuery(sql, null);
	}

	public Cursor fetchBuys() {
		String sql =
			"SELECT BU._id AS _id, " + DBHelper.BUY_KEY_AMOUNT + 
			", " + DBHelper.BUY_KEY_DATE +
			", CA." + DBHelper.CATEGORY_KEY_NAME + " AS catname"+ 
			", CP." + DBHelper.COMPANY_KEY_NAME + " AS copname"+ 
			" FROM " + DBHelper.BUY_TABLE_NAME + " AS BU" +
			" INNER JOIN " + DBHelper.CATEGORY_TABLE_NAME + " AS CA ON CA._id=" + DBHelper.CATEGORY_KEY_ID +
			" INNER JOIN " + DBHelper.COMPANY_TABLE_NAME + " AS CP ON CP._id=" + DBHelper.COMPANY_KEY_ID +
			" INNER JOIN " + DBHelper.BUYCATEGORY_TABLE_NAME + " AS BCA ON BU._id=BCA." + DBHelper.BUY_KEY_ID +
			" INNER JOIN " + DBHelper.BUYCOMPANY_TABLE_NAME + " AS BCP ON BU._id=BCP." + DBHelper.BUY_KEY_ID +
			" ORDER BY " + DBHelper.BUY_KEY_DATE + " DESC";
		return mDb.rawQuery(sql, null);
	}
	
	public Cursor fetchBuysCategory(long categoryId) {
		String sql =
			"SELECT _id," + DBHelper.BUY_KEY_AMOUNT + 
			", " + DBHelper.BUY_KEY_DATE + 
			" FROM " + DBHelper.BUY_TABLE_NAME +
			" INNER JOIN " + DBHelper.BUYCATEGORY_TABLE_NAME + " ON " +
			DBHelper.BUYCATEGORY_TABLE_NAME + "." + DBHelper.BUY_KEY_ID + " = " +
			DBHelper.BUY_TABLE_NAME + "._id" + 
			" WHERE " + DBHelper.CATEGORY_KEY_ID + " = " + categoryId; 
		return mDb.rawQuery(sql, null);
	}

	public Cursor fetchBuysCompany(long companyId) {
		String sql =
			"SELECT _id," + DBHelper.BUY_KEY_AMOUNT + 
			", " + DBHelper.BUY_KEY_DATE + 
			" FROM " + DBHelper.BUY_TABLE_NAME +
			" INNER JOIN " + DBHelper.BUYCOMPANY_TABLE_NAME + " ON " +
			DBHelper.BUYCOMPANY_TABLE_NAME + "." + DBHelper.BUY_KEY_ID + " = " +
			DBHelper.BUY_TABLE_NAME + "._id" + 
			" WHERE " + DBHelper.COMPANY_KEY_ID + " = " + companyId; 
		return mDb.rawQuery(sql, null);
	}

	
	public Cursor fetchCategories() {
		String sql =
			"SELECT _id, " + DBHelper.CATEGORY_KEY_NAME +
			" FROM " + DBHelper.CATEGORY_TABLE_NAME +
			" ORDER BY " + DBHelper.CATEGORY_KEY_NAME;
		return mDb.rawQuery(sql, null);
	}

	public Cursor fetchCategory(long id) {
		String sql = 
			"SELECT _id, " + DBHelper.CATEGORY_KEY_NAME + 
			" FROM " + DBHelper.CATEGORY_TABLE_NAME +
			" WHERE _id = " + id;
		return mDb.rawQuery(sql, null);
	}

	public Cursor fetchCompanies() {
		String sql =
			"SELECT _id, " + DBHelper.COMPANY_KEY_NAME + 
			" FROM " + DBHelper.COMPANY_TABLE_NAME +
			" ORDER BY " + DBHelper.CATEGORY_KEY_NAME;
		return mDb.rawQuery(sql, null);
	}

	public Cursor fetchCompanies(long categoryId) {
		String sql = 
			"SELECT " + DBHelper.COMPANY_KEY_NAME +
			" FROM " + mDBHelper.COMPANY_TABLE_NAME +
			" INNER JOIN " + mDBHelper.COMPANYCATEGORY_TABLE_NAME + " ON " +
			mDBHelper.COMPANYCATEGORY_TABLE_NAME + "." + mDBHelper.COMPANY_KEY_ID + " = " +
			mDBHelper.COMPANY_TABLE_NAME + "._id" + 
			" WHERE " + mDBHelper.CATEGORY_KEY_ID + " = " + categoryId; 
		return mDb.rawQuery(sql, null);
	}

	public Cursor fetchCompany(long id) {
		String sql =
			"SELECT " + mDBHelper.COMPANY_KEY_NAME +
			" FROM " + mDBHelper.COMPANY_TABLE_NAME +
			" WHERE _id = " + id;
		return mDb.rawQuery(sql, null);
	}

	public void updateBuy(long id, double newAmount) {
		ContentValues updatedValues = new ContentValues();
		updatedValues.put(mDBHelper.BUY_KEY_AMOUNT, newAmount);
		
		mDb.update(mDBHelper.BUY_TABLE_NAME, updatedValues, 
				"_id = " + id, null);
	}

	public void updateCategory(long id, String newCategoryName) {
		ContentValues updatedValues = new ContentValues();
		updatedValues.put(mDBHelper.CATEGORY_KEY_NAME, newCategoryName);
		
		mDb.update(mDBHelper.CATEGORY_TABLE_NAME, updatedValues,
				"_id =" + id, null);
	}

	public void updateCompany(long id, String newCompanyName) {
		ContentValues updatedValues = new ContentValues();
		updatedValues.put(mDBHelper.COMPANY_KEY_NAME, newCompanyName);
		
		mDb.update(mDBHelper.COMPANY_TABLE_NAME, updatedValues, 
				"_id =" + id, null);
	}
	
	public void addToAccount(double amount) {
		double current = getAccount();
		ContentValues values = new ContentValues();
		if(current > Double.NEGATIVE_INFINITY){
			values.put(mDBHelper.ACCOUNT_KEY_CURRENT, current + amount);
			mDb.update(mDBHelper.ACCOUNT_TABLE_NAME, values, null, null);
		}
		else{
			values.put(mDBHelper.ACCOUNT_KEY_CURRENT, amount);
			mDb.insert(DBHelper.ACCOUNT_TABLE_NAME, null, values);
		}
	}
	
	public void lowerAccount(double amount) {
		double current = getAccount();
		ContentValues values = new ContentValues();
		values.put(mDBHelper.ACCOUNT_KEY_CURRENT, current - amount);
		mDb.update(mDBHelper.ACCOUNT_TABLE_NAME, values, null, null);
	}
	
	public double getAccount() {
		String sql = 
			"SELECT " + mDBHelper.ACCOUNT_KEY_CURRENT + 
			" FROM " + mDBHelper.ACCOUNT_TABLE_NAME + "";
		Cursor cursor = mDb.rawQuery(sql, null);
		if(cursor.getCount() > 0){
			cursor.moveToFirst();
			return cursor.getDouble(cursor.getColumnIndex(mDBHelper.ACCOUNT_KEY_CURRENT));
		}
		else
		{
			return Double.MIN_VALUE;
		}
	}
	
	public boolean databaseOpen(){
		return mDb.isOpen();
	}
	
	public boolean databaseClosed(){
		return !mDb.isOpen();
	}
	
	public void clearDatabase(){
		mDBHelper.clearDatabase(mDb);
	}

}
